package com.apigen.platform.service.impl;

import com.apigen.platform.dto.DatabaseConnectionRequest;
import com.apigen.platform.dto.TableImportRequest;
import com.apigen.platform.entity.GenTable;
import com.apigen.platform.entity.GenTableField;
import com.apigen.platform.mapper.GenTableMapper;
import com.apigen.platform.mapper.GenTableFieldMapper;
import com.apigen.platform.service.IDatabaseService;
import com.apigen.platform.service.DatabaseMetadataService;
import com.apigen.platform.vo.DatabaseTableVO;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据库服务实现类
 *
 * @author API Generator Platform
 * @since 2025-09-24
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class DatabaseServiceImpl implements IDatabaseService {

    private final GenTableMapper genTableMapper;
    private final GenTableFieldMapper genTableFieldMapper;
    private final DatabaseMetadataService databaseMetadataService;

    @Override
    public Map<String, Object> testConnection(DatabaseConnectionRequest connectionRequest) {
        return databaseMetadataService.testConnection(connectionRequest);
    }

    @Override
    public List<DatabaseTableVO> getDatabaseTables(DatabaseConnectionRequest connectionRequest) {
        return databaseMetadataService.getAllTables(connectionRequest);
    }

    @Override
    public DatabaseTableVO getTableDetail(DatabaseConnectionRequest connectionRequest, String tableName) {
        try (Connection connection = createConnection(connectionRequest)) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            DatabaseTableVO table = null;
            
            // 获取表基本信息
            try (ResultSet rs = metaData.getTables(connectionRequest.getDatabase(), null, tableName, new String[]{"TABLE", "VIEW"})) {
                if (rs.next()) {
                    table = DatabaseTableVO.builder()
                            .tableName(rs.getString("TABLE_NAME"))
                            .tableComment(rs.getString("REMARKS"))
                            .tableType(rs.getString("TABLE_TYPE"))
                            .build();
                }
            }
            
            if (table == null) {
                throw new RuntimeException("表 " + tableName + " 不存在");
            }
            
            // 获取字段信息
            List<DatabaseTableVO.DatabaseFieldVO> fields = new ArrayList<>();
            try (ResultSet rs = metaData.getColumns(connectionRequest.getDatabase(), null, tableName, null)) {
                while (rs.next()) {
                    DatabaseTableVO.DatabaseFieldVO field = DatabaseTableVO.DatabaseFieldVO.builder()
                            .fieldName(rs.getString("COLUMN_NAME"))
                            .fieldType(rs.getString("TYPE_NAME"))
                            .fieldLength(rs.getInt("COLUMN_SIZE"))
                            .fieldScale(rs.getInt("DECIMAL_DIGITS"))
                            .isNullable("YES".equals(rs.getString("IS_NULLABLE")))
                            .defaultValue(rs.getString("COLUMN_DEF"))
                            .fieldComment(rs.getString("REMARKS"))
                            .isAutoIncrement("YES".equals(rs.getString("IS_AUTOINCREMENT")))
                            .sortOrder(rs.getInt("ORDINAL_POSITION"))
                            .build();
                    
                    // 设置Java类型
                    field.setJavaType(getJavaType(field.getFieldType()));
                    field.setJdbcType(getJdbcType(field.getFieldType()));
                    
                    fields.add(field);
                }
            }
            
            // 获取主键信息
            Set<String> primaryKeys = new HashSet<>();
            try (ResultSet rs = metaData.getPrimaryKeys(connectionRequest.getDatabase(), null, tableName)) {
                while (rs.next()) {
                    primaryKeys.add(rs.getString("COLUMN_NAME"));
                }
            }
            
            // 设置主键标识
            fields.forEach(field -> field.setIsPrimaryKey(primaryKeys.contains(field.getFieldName())));
            
            // 获取索引信息
            List<DatabaseTableVO.DatabaseIndexVO> indexes = new ArrayList<>();
            try (ResultSet rs = metaData.getIndexInfo(connectionRequest.getDatabase(), null, tableName, false, false)) {
                Map<String, List<String>> indexColumns = new HashMap<>();
                Map<String, Boolean> indexUnique = new HashMap<>();
                
                while (rs.next()) {
                    String indexName = rs.getString("INDEX_NAME");
                    if (indexName != null && !"PRIMARY".equals(indexName)) {
                        String columnName = rs.getString("COLUMN_NAME");
                        boolean unique = !rs.getBoolean("NON_UNIQUE");
                        
                        indexColumns.computeIfAbsent(indexName, k -> new ArrayList<>()).add(columnName);
                        indexUnique.put(indexName, unique);
                    }
                }
                
                indexColumns.forEach((indexName, columns) -> {
                    DatabaseTableVO.DatabaseIndexVO index = DatabaseTableVO.DatabaseIndexVO.builder()
                            .indexName(indexName)
                            .indexType("NORMAL")
                            .isUnique(indexUnique.get(indexName))
                            .indexColumns(String.join(",", columns))
                            .build();
                    indexes.add(index);
                });
            }
            
            table.setFields(fields);
            table.setIndexes(indexes);
            table.setFieldCount(fields.size());
            table.setIndexCount(indexes.size());
            
            log.info("获取表{}详细信息成功", tableName);
            
            return table;
            
        } catch (Exception e) {
            log.error("获取表详细信息失败: {}", e.getMessage());
            throw new RuntimeException("获取表详细信息失败: " + e.getMessage());
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> importTables(DatabaseConnectionRequest connectionRequest, TableImportRequest importRequest) {
        Map<String, Object> result = new HashMap<>();
        List<String> successTables = new ArrayList<>();
        List<String> failedTables = new ArrayList<>();
        
        try {
            for (String tableName : importRequest.getTableNames()) {
                try {
                    // 获取表详细信息
                    DatabaseTableVO tableDetail = getTableDetail(connectionRequest, tableName);
                    
                    // 检查表是否已存在
                    LambdaQueryWrapper<GenTable> queryWrapper = new LambdaQueryWrapper<>();
                    queryWrapper.eq(GenTable::getTableName, tableName)
                               .eq(GenTable::getProjectId, importRequest.getProjectId());
                    GenTable existingTable = genTableMapper.selectOne(queryWrapper);
                    
                    if (existingTable != null && !importRequest.getOverwrite()) {
                        failedTables.add(tableName + "(已存在)");
                        continue;
                    }
                    
                    // 创建或更新表记录
                    GenTable genTable = new GenTable();
                    if (existingTable != null) {
                        genTable.setTableId(existingTable.getTableId());
                    }
                    genTable.setTableName(tableName);
                    genTable.setTableComment(tableDetail.getTableComment());
                    genTable.setTableType(tableDetail.getTableType());
                    genTable.setEngine(tableDetail.getEngine());
                    genTable.setCharset(tableDetail.getCharset());
                    genTable.setCollation(tableDetail.getCollation());
                    genTable.setAutoIncrement(tableDetail.getAutoIncrement());
                    genTable.setProjectId(importRequest.getProjectId());
                    genTable.setCreatorId(1000L); // TODO: 从当前用户获取
                    
                    if (existingTable != null) {
                        genTableMapper.updateById(genTable);
                        // 删除旧字段
                        LambdaQueryWrapper<GenTableField> fieldQueryWrapper = new LambdaQueryWrapper<>();
                        fieldQueryWrapper.eq(GenTableField::getTableId, genTable.getTableId());
                        genTableFieldMapper.delete(fieldQueryWrapper);
                    } else {
                        genTableMapper.insert(genTable);
                    }
                    
                    // 导入字段信息
                    for (DatabaseTableVO.DatabaseFieldVO field : tableDetail.getFields()) {
                        GenTableField genField = new GenTableField();
                        genField.setTableId(genTable.getTableId());
                        genField.setFieldName(field.getFieldName());
                        genField.setFieldType(field.getFieldType());
                        genField.setFieldLength(field.getFieldLength());
                        genField.setFieldScale(field.getFieldScale());
                        genField.setIsNullable(field.getIsNullable());
                        genField.setIsPrimaryKey(field.getIsPrimaryKey());
                        genField.setIsAutoIncrement(field.getIsAutoIncrement());
                        genField.setDefaultValue(field.getDefaultValue());
                        genField.setFieldComment(field.getFieldComment());
                        genField.setSortOrder(field.getSortOrder());
                        
                        genTableFieldMapper.insert(genField);
                    }
                    
                    successTables.add(tableName);
                    
                } catch (Exception e) {
                    log.error("导入表{}失败: {}", tableName, e.getMessage());
                    failedTables.add(tableName + "(" + e.getMessage() + ")");
                }
            }
            
            result.put("success", true);
            result.put("message", String.format("导入完成，成功%d张表，失败%d张表", successTables.size(), failedTables.size()));
            result.put("successTables", successTables);
            result.put("failedTables", failedTables);
            result.put("successCount", successTables.size());
            result.put("failedCount", failedTables.size());
            
        } catch (Exception e) {
            log.error("批量导入表失败: {}", e.getMessage());
            result.put("success", false);
            result.put("message", "批量导入表失败: " + e.getMessage());
        }
        
        return result;
    }

    @Override
    public Page<DatabaseTableVO> getImportedTables(Long projectId, Long current, Long size, String tableName) {
        Page<GenTable> page = new Page<>(current, size);
        LambdaQueryWrapper<GenTable> queryWrapper = new LambdaQueryWrapper<>();
        
        if (projectId != null) {
            queryWrapper.eq(GenTable::getProjectId, projectId);
        }
        if (StringUtils.hasText(tableName)) {
            queryWrapper.like(GenTable::getTableName, tableName);
        }
        queryWrapper.orderByDesc(GenTable::getCreateTime);
        
        Page<GenTable> genTablePage = genTableMapper.selectPage(page, queryWrapper);
        
        // 转换为VO
        Page<DatabaseTableVO> resultPage = new Page<>(current, size);
        resultPage.setTotal(genTablePage.getTotal());
        
        List<DatabaseTableVO> records = genTablePage.getRecords().stream().map(genTable -> {
            // 获取字段数量
            LambdaQueryWrapper<GenTableField> fieldQueryWrapper = new LambdaQueryWrapper<>();
            fieldQueryWrapper.eq(GenTableField::getTableId, genTable.getTableId());
            Long fieldCount = genTableFieldMapper.selectCount(fieldQueryWrapper);
            
            return DatabaseTableVO.builder()
                    .tableId(genTable.getTableId())
                    .tableName(genTable.getTableName())
                    .tableComment(genTable.getTableComment())
                    .tableType(genTable.getTableType())
                    .engine(genTable.getEngine())
                    .charset(genTable.getCharset())
                    .collation(genTable.getCollation())
                    .autoIncrement(genTable.getAutoIncrement())
                    .projectId(genTable.getProjectId())
                    .creatorId(genTable.getCreatorId())
                    .createTime(genTable.getCreateTime())
                    .updateTime(genTable.getUpdateTime())
                    .fieldCount(fieldCount.intValue())
                    .build();
        }).collect(Collectors.toList());
        
        resultPage.setRecords(records);
        
        return resultPage;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean deleteTables(List<Long> tableIds) {
        try {
            // 删除表记录
            int deletedTables = genTableMapper.deleteBatchIds(tableIds);
            
            // 删除字段记录
            for (Long tableId : tableIds) {
                LambdaQueryWrapper<GenTableField> queryWrapper = new LambdaQueryWrapper<>();
                queryWrapper.eq(GenTableField::getTableId, tableId);
                genTableFieldMapper.delete(queryWrapper);
            }
            
            log.info("删除{}张表成功", deletedTables);
            return true;
            
        } catch (Exception e) {
            log.error("删除表失败: {}", e.getMessage());
            throw new RuntimeException("删除表失败: " + e.getMessage());
        }
    }

    @Override
    public Boolean updateTableInfo(Long tableId, String tableComment) {
        try {
            GenTable genTable = new GenTable();
            genTable.setTableId(tableId);
            genTable.setTableComment(tableComment);
            genTable.setUpdateTime(LocalDateTime.now());
            
            int updated = genTableMapper.updateById(genTable);
            return updated > 0;
            
        } catch (Exception e) {
            log.error("更新表信息失败: {}", e.getMessage());
            return false;
        }
    }

    @Override
    public Map<String, Object> syncTableStructure(Long tableId, DatabaseConnectionRequest connectionRequest) {
        // TODO: 实现表结构同步逻辑
        Map<String, Object> result = new HashMap<>();
        result.put("success", true);
        result.put("message", "表结构同步功能待实现");
        return result;
    }

    @Override
    public List<Map<String, Object>> getTableRelations(Long projectId) {
        // TODO: 实现表关联关系查询逻辑
        return new ArrayList<>();
    }

    @Override
    public String generateTableDDL(Long tableId) {
        // TODO: 实现DDL生成逻辑
        return "-- DDL生成功能待实现";
    }

    /**
     * 创建数据库连接
     */
    private Connection createConnection(DatabaseConnectionRequest request) throws SQLException {
        String url = buildConnectionUrl(request);
        Properties props = new Properties();
        props.setProperty("user", request.getUsername());
        if (StringUtils.hasText(request.getPassword())) {
            props.setProperty("password", request.getPassword());
        }
        props.setProperty("useUnicode", "true");
        props.setProperty("characterEncoding", request.getCharset());
        props.setProperty("useSSL", String.valueOf(request.getUseSSL()));
        props.setProperty("serverTimezone", "Asia/Shanghai");
        
        DriverManager.setLoginTimeout(request.getTimeout());
        return DriverManager.getConnection(url, props);
    }

    /**
     * 构建连接URL
     */
    private String buildConnectionUrl(DatabaseConnectionRequest request) {
        StringBuilder url = new StringBuilder();
        
        switch (request.getDatabaseType().toUpperCase()) {
            case "MYSQL":
                url.append("jdbc:mysql://")
                   .append(request.getHost())
                   .append(":")
                   .append(request.getPort())
                   .append("/")
                   .append(request.getDatabase());
                break;
            case "POSTGRESQL":
                url.append("jdbc:postgresql://")
                   .append(request.getHost())
                   .append(":")
                   .append(request.getPort())
                   .append("/")
                   .append(request.getDatabase());
                break;
            default:
                throw new RuntimeException("不支持的数据库类型: " + request.getDatabaseType());
        }
        
        if (StringUtils.hasText(request.getConnectionParams())) {
            url.append("?").append(request.getConnectionParams());
        }
        
        return url.toString();
    }

    /**
     * 获取表名模式
     */
    private String getTableNamePattern(String tableFilter) {
        if (!StringUtils.hasText(tableFilter)) {
            return null;
        }
        return tableFilter.replace("*", "%");
    }

    /**
     * 获取表字段数量
     */
    private Integer getTableFieldCount(Connection connection, String tableName) {
        try (PreparedStatement stmt = connection.prepareStatement(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?")) {
            stmt.setString(1, tableName);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        } catch (Exception e) {
            log.warn("获取表{}字段数量失败: {}", tableName, e.getMessage());
        }
        return 0;
    }

    /**
     * 获取Java类型
     */
    private String getJavaType(String sqlType) {
        switch (sqlType.toUpperCase()) {
            case "TINYINT":
            case "SMALLINT":
            case "MEDIUMINT":
            case "INT":
            case "INTEGER":
                return "Integer";
            case "BIGINT":
                return "Long";
            case "FLOAT":
                return "Float";
            case "DOUBLE":
                return "Double";
            case "DECIMAL":
            case "NUMERIC":
                return "BigDecimal";
            case "CHAR":
            case "VARCHAR":
            case "TINYTEXT":
            case "TEXT":
            case "MEDIUMTEXT":
            case "LONGTEXT":
                return "String";
            case "DATE":
                return "LocalDate";
            case "TIME":
                return "LocalTime";
            case "DATETIME":
            case "TIMESTAMP":
                return "LocalDateTime";
            case "BIT":
            case "BOOLEAN":
                return "Boolean";
            default:
                return "String";
        }
    }

    /**
     * 获取JDBC类型
     */
    private String getJdbcType(String sqlType) {
        return sqlType.toUpperCase();
    }
}











